import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
# from fastapi import Request
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

check_set_router = APIRouter(prefix="/inspection/checkset", tags=["巡查设定管理"],
                             dependencies=[Depends(oauth2_scheme)])


class CheckSettingSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    feuipmentid: int = Field(-1, description="设备ID，-1表示不筛选")
    fchecktypeno: Optional[str] = Field("", description="检查类型编号")
    fequipmentname: Optional[str] = Field("", description="设备名称（预留字段）")


@check_set_router.get("/list", summary="巡查设定查询接口")
def list_check_set(search: CheckSettingSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from s_ts_equipment_checkset where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.feuipmentid != -1:
        sql = sql + " and feuipmentid = :feuipmentid "
        param["feuipmentid"] = search.feuipmentid
    if search.fchecktypeno != "":
        sql = sql + " and fchecktypeno = :fchecktypeno "
        param["fchecktypeno"] = search.fchecktypeno
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fsite_no,feuipmentid,fchecktypeno,fcheckday,fmemo,fupddate,foperator"
    sql = sql + " from s_ts_equipment_checkset where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.feuipmentid != -1:
        sql = sql + " and feuipmentid = :feuipmentid "
        param["feuipmentid"] = search.feuipmentid
    if search.fchecktypeno != "":
        sql = sql + " and fchecktypeno = :fchecktypeno "
        param["fchecktypeno"] = search.fchecktypeno
    if search.order == "desc":
        sql = sql + " order by  feuipmentid,fchecktypeno desc "
    else:
        sql = sql + " order by  feuipmentid,fchecktypeno asc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteno'] = data[key][0]
        d1['feuipmentid'] = data[key][1]
        d1['fchecktypeno'] = data[key][2]
        d1['fcheckday'] = data[key][3]
        d1['fmemo'] = data[key][4]
        d1['fupddate'] = data[key][5]
        d1['foperator'] = data[key][6]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": search.limit, "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class CheckSettingCreate(BaseModel):
    """巡查设定新增请求参数"""
    fsiteNo: str = Field(..., description="站点编号")
    feuipmentid: int = Field(..., description="设备ID")
    fchecktypeno: str = Field(..., description="检查类型编号")
    fcheckday: int = Field(..., description="检查周期（天）")
    fmemo: Optional[str] = Field("", description="备注")
    foperator: str = Field(..., description="操作人")
    fupddate: str = Field(..., description="更新日期(yyyy-MM-dd HH:mm:ss)")


@check_set_router.post("/create", summary="巡查设定新增接口")
def create_check_set(create: CheckSettingCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select fsite_no from s_ts_equipment_checkset "
            sql = sql + " where fsite_no= :fsiteNo  and feuipmentid= :feuipmentid  and fchecktypeno= :fchecktypeno "
            data = db.execute(text(sql), {
                "fsiteNo": create.fsiteNo,
                "feuipmentid": create.feuipmentid,
                "fchecktypeno": create.fchecktypeno,
            }).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="巡查类型已存在")
            else:
                sql = "insert into s_ts_equipment_checkset(fsite_no,feuipmentid,fchecktypeno,fcheckday,fmemo,fupddate,foperator)"
                sql = sql + " values( :fsiteNo , :feuipmentid , :fchecktypeno , :fcheckday , :fmemo , :fupddate , :foperator );"
                db.execute(text(sql), {
                    "fsiteNo": create.fsiteNo,
                    "feuipmentid": create.feuipmentid,
                    "fchecktypeno": create.fchecktypeno,
                    "fcheckday": create.fcheckday,
                    "fmemo": create.fmemo,
                    "fupddate": create.fupddate,
                    "foperator": create.foperator,
                })

                data = {"fcheckday": create.fcheckday
                    , "fchecktypeno": create.fchecktypeno
                    , "feuipmentid": create.feuipmentid
                    , "fmemo": create.fmemo
                    , "fsiteNo": create.fsiteNo
                    , "foperator": create.foperator
                    , "fupddate": create.fupddate
                        }
                return BaseResponse.success(data=data)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="未知错误")


@check_set_router.post("/update", summary="巡查设定修改接口")
def update_check_set(update: CheckSettingCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "update s_ts_equipment_checkset"
            sql = sql + " set fmemo= :fmemo ,fcheckday= :fcheckday "
            sql = sql + " ,fupddate= :fupddate ,foperator= :foperator "
            sql = sql + " where fsite_no= :fsiteNo  and feuipmentid= :feuipmentid  and fchecktypeno= :fchecktypeno"
            db.execute(text(sql), {
                "fmemo": update.fmemo,
                "fcheckday": update.fcheckday,
                "fupddate": update.fupddate,
                "foperator": update.foperator,
                "fsiteNo": update.fsiteNo,
                "fchecktypeno": update.fchecktypeno,
                "feuipmentid": update.feuipmentid,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="未知错误")


class CheckSettingDelete(BaseModel):
    """巡查设定删除请求参数"""
    fsiteNo: str = Field(..., description="站点编号")
    feuipmentid: int = Field(..., description="设备ID")
    fchecktypeno: str = Field(..., description="检查类型编号")


@check_set_router.post("/delete", summary="巡查设定删除接口")
def delete_check_set(delete: CheckSettingDelete, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from s_ts_equipment_checkset "
            sql = sql + " where fsite_no= :fsiteNo  and feuipmentid= :feuipmentid  and fchecktypeno= :fchecktypeno "
            db.execute(text(sql), {
                "fsiteNo": delete.fsiteNo,
                "feuipmentid": delete.feuipmentid,
                "fchecktypeno": delete.fchecktypeno,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="未知错误")
